package com.b511.swpu.cementbackend.util;

import com.b511.swpu.cementbackend.model.DataColumn;
import com.b511.swpu.cementbackend.model.DataRow;
import com.b511.swpu.cementbackend.model.DataTable;
import lombok.Getter;
import lombok.Setter;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class DBHelper {

    private static String DRIVER = "oracle.jdbc.driver.OracleDriver"; // 驱动标识符 配置文件读取

    private static String URL; // 链接字符串 配置文件读取

    private static String USER;

    private static String PASSWORD;

    @Value("${db.url}")
    public void setURL(String URL) {
        DBHelper.URL = URL;
    }

    @Value("${db.user}")
    public void setUSER(String USER) {
        DBHelper.USER = USER;
    }

    @Value("${db.password}")
    public void setPASSWORD(String PASSWORD) {
        DBHelper.PASSWORD = PASSWORD;
    }

    /**
     * 连接数据库
     *
     * @return 链接数据库对象
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 释放相应的资源
     *
     * @param rs
     * @param pstmt
     * @param conn
     */
    public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void closeAll(PreparedStatement pstmt, Connection conn) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 此方法可以完成增删改所有的操作
     *
     * @param sql
     * @param params
     * @return true or false
     */
    public boolean excuteUpdate(String sql, List<Object> params) {
        int res = 0;//受影响的行数
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            res = pstmt.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return res > 0 ? true : false;
    }

    /**
     * 使用泛型方法和反射机制进行封装
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     */
    public <T> List<T> executeQuery(String sql, List<Object> params, Class<T> cls) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<T> data = new ArrayList<T>();
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            //把查询出来的记录封装成对应的实体类对象
            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，包括，列名，列的个数，列的数据类型
            while (rs.next()) {
                T m = cls.newInstance();
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    String col_name = rsd.getColumnName(i + 1);//获得列名
                    Object value = rs.getObject(col_name);//获得列所对应的值
                    Field field = cls.getDeclaredField(col_name);
                    field.setAccessible(true);//给私有属性设置可访问权
                    field.set(m, value);//给对象的私有属性赋值
                }
                data.add(m);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return data;
    }

    public ResultSet executeQuery1(String sql, List<Object> params) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，
            DataTable dt = new DataTable();

            for (int i = 0; i < rsd.getColumnCount(); i++) {
                String col_name = rsd.getColumnName(i + 1);//获得列名
                String col_type = rsd.getColumnTypeName(i + 1);//列类型
                DataColumn col = new DataColumn(col_name, col_name);
                System.out.print(col_name + " \t" );
                dt.Columns.add(col);
            }
            System.out.println("--------------------------");
            while (rs.next()) {
                DataRow dr = dt.NewRow();
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    dr.cells[i] = rs.getString(i + 1);
                    // System.out.print(dr.cells[i] + "\t");
                }
                // System.out.println(" ");
            }

//            System.out.println("*************************");
//            for(DataRow dr:dt.Rows){
//                for(int col=0;col<dt.Columns.size();col++){
//                    System.out.print(dr.cells[col] + "\t");
//                }
//                System.out.println(" ");
//            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return rs;
    }

    public DataTable executeQuery(String sql, List<Object> params) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        DataTable dt = new DataTable();
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，


            for (int i = 0; i < rsd.getColumnCount(); i++) {
                String col_name = rsd.getColumnName(i + 1);//获得列名
                String col_type = rsd.getColumnTypeName(i + 1);//列类型
                DataColumn col = new DataColumn(col_name, col_name);
                System.out.print(col_name + " \t" );
                dt.Columns.add(col);
            }
            System.out.println("--------------------------");
            while (rs.next()) {
                DataRow dr = dt.NewRow();
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    dr.cells[i] = rs.getString(i + 1);
                    System.out.print(dr.cells[i] + "\t");
                }
                System.out.println(" ");
            }
            System.out.println("*************************");
            for(DataRow dr:dt.Rows){
                for(int col=0;col<dt.Columns.size();col++){
                    System.out.print(dr.cells[col] + "\t");
                }
                System.out.println(" ");
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return dt;
    }

    // 返回Json格式
    public String executeQueryJson(String sql, List<Object> params) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Statement st = null;
        ResultSet rs = null;
        String json="";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，

            for (int i = 0; i < rsd.getColumnCount(); i++) {
                String col_name = rsd.getColumnName(i + 1);//获得列名
                String col_type = rsd.getColumnTypeName(i + 1);//列类型
                DataColumn col = new DataColumn(col_name, col_name);
                // System.out.print(col_name + " \t" );

            }
            // System.out.println("--------------------------");
            StringBuilder jsonbuilder=new StringBuilder();
            jsonbuilder.append("[");
            while (rs.next()) {
                jsonbuilder.append("{");
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    jsonbuilder.append( "\"" +rsd.getColumnName(i + 1)+ "\"" + ":");
                    jsonbuilder.append("\""+  rs.getString(i + 1)+"\",");
                }
                jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
                jsonbuilder.append("},");

            }

            jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
            jsonbuilder.append("]");
            // System.out.println("*************************");
            json=jsonbuilder.toString();
            // System.out.println(json);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return json;
    }

    //执行单个统计查询
    public int executeSingleQuery(String sql, List<Object> params) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int result=0;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();


            while (rs.next()) {
                result=rs.getInt(1);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return result;
    }
    // 执行分页查询，返回Json
    public String executeQueryJsonByPage(String sql,List<Object> params,int pageSize,int page) throws Exception {
        int start=(page-1)*pageSize+1;
        int end=pageSize*page;

        String pageSql="SELECT * FROM ( select A.*,ROWNUM RN  FROM ( %s ) A  WHERE ROWNUM<=%d)WHERE RN>=%d";
        sql= String.format(pageSql,sql,end,start);
        System.out.println(sql);
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String json="";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            if (params != null) {
                //加入有？占位符，在执行之前把？占位符替换掉
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，

           /*
            for (int i = 0; i < rsd.getColumnCount(); i++) {
                String col_name = rsd.getColumnName(i + 1);//获得列名
                String col_type = rsd.getColumnTypeName(i + 1);//列类型
                DataColumn col = new DataColumn(col_name, col_name);
                System.out.print(col_name + " \t" );

            }
           */

            // System.out.println("--------------------------");
            StringBuilder jsonbuilder=new StringBuilder();
            jsonbuilder.append("[");
//            while (rs.next()) {
//                jsonbuilder.append("{");
//                for (int i = 0; i < rsd.getColumnCount(); i++) {
//                    jsonbuilder.append("\""+ rsd.getColumnName(i + 1)+"\":");
//                    if(rs.getString(i + 1)!= null){
//                        jsonbuilder.append("\""+  rs.getString(i + 1)+"\",");
//                    }else {
//                        // 判断空值
//                        jsonbuilder.append( "\"" + "\",");
//                    }
//                }
//                jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
//                jsonbuilder.append("},");
//
//            }
            while (rs.next()) {
                jsonbuilder.append("{");
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    jsonbuilder.append("\""+ rsd.getColumnName(i + 1)+"\":");
                    if(rs.getString(i + 1)!= null){
                        jsonbuilder.append("\""+  rs.getString(i + 1).replace("\"","\\\"")+"\",");
                    }else {
                        // 判断空值
                        jsonbuilder.append( "\"" + "\",");
                    }
                }
                jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
                jsonbuilder.append("},");

            }

            jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
            jsonbuilder.append("]");
            // System.out.println("*************************");
            json=jsonbuilder.toString();
            // System.out.println(json);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return json;
    }

    public JSONObject execQuery(String sql) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        JSONObject jsonobj = new JSONObject();
        String json="";
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            rs = pstmt.executeQuery();


            ResultSetMetaData rsd = rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，
            // System.out.println("--------------------------");
            StringBuilder jsonbuilder=new StringBuilder();
            jsonbuilder.append("[");
            while (rs.next()) {
                jsonbuilder.append("{");
                for (int i = 0; i < rsd.getColumnCount(); i++) {
                    jsonbuilder.append("\""+ rsd.getColumnName(i + 1)+"\":");
                    if(rs.getString(i + 1)!= null){
                        jsonbuilder.append("\""+  rs.getString(i + 1).replace("\"","\\\"")+"\",");
                    }else {
                        // 判断空值
                        jsonbuilder.append( "\"" + "\",");
                    }
                }
                jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
                jsonbuilder.append("},");

            }
            //如果查询为空，加上下面的语句会导致删除‘[’,发生错误
//            jsonbuilder.deleteCharAt(jsonbuilder.toString().length()-1);//删除多余的逗号，
            jsonbuilder.append("]");
            // System.out.println("*************************");
            json=jsonbuilder.toString();
            // System.out.println(json);

            JSONArray jarr = JSONArray.fromObject(json);
            jsonobj.put("result",jarr);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return jsonobj;
    }

    public int execUpdate(String sql) throws Exception {
        System.out.println(sql);
        Connection conn = null;
        PreparedStatement pstmt = null;
        int count = 0;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);//装载sql语句
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            closeAll(pstmt, conn);
        }
        return count;
    }
}

